﻿using System;
using System.Data;

namespace org.se.fs.model
{
    public abstract class DataContext
    {
        public static ProviderType ProviderType { get; set; }
        public static string ConnectionString { get; set; }

        protected DataContext() { }

        protected DataContext(string connectionString)
        {
            ConnectionString = connectionString;
        }

        protected DataContext(ProviderType providerType, string connectionString)
        {
            ProviderType = providerType;
            ConnectionString = connectionString;
        }

        internal static IDatabaseManager GetDatabase()
        {
            return new DatabaseManager(ProviderType, ConnectionString);
        }

        internal static IDatabaseManager GetDatabase(string connectionString)
        {
            return new DatabaseManager(ProviderType, connectionString);
        }

        public bool TestConnection(string connectionString)
        {
            try
            {
                bool isSuccess;
                using (var databaseManager = GetDatabase(connectionString))
                { isSuccess = databaseManager.Ping(); }
                return isSuccess;
            }
            catch (Exception)
            {
                return false;
            }
        }

        protected static DataTable ToDataTable(DataSet dataSet)
        {
            if (dataSet == null)
                return null;
            return dataSet.Tables.Count.Equals(0) ? null : dataSet.Tables[0];
        }

        public static string GetConnectionString(string user, string password, string server, string database)
        {
            switch (ProviderType)
            {
                case ProviderType.Mysql:
                    return string.Format("Server={0};Database={1};Uid={2};Pwd={3};", server, database, user, password);

                case ProviderType.Odbc:
                    return "";

                case ProviderType.OleDb:
                    return "";

                case ProviderType.Oracle:
                    return string.Format("Data Source = {0}; User Id = {1}; Password = {2}; Integrated Security = no;", database, user, password);

                case ProviderType.SqlServer:
                    return string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", server, database, user, password);
            }
            return string.Empty;
        }

        //public bool ToggleActiveByTable(string tableName, UInt32 id, bool active)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "tableName", tableName);
        //            databaseManager.AddParameters(1, "id", id);
        //            databaseManager.AddParameters(2, "active", active);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "ToggleActiveByTable"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //#region User Table
        //public UInt32 GetUserCount(string username, string password)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "username", username);
        //            databaseManager.AddParameters(1, "userpass", password);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "GetUserCount"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;    
        //    }
        //}

        //public DataTable GetUser(UInt32 uid)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "id", uid);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetUser");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public DataTable GetUserAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetUserAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertUser(string idNumber, string surname, string firstname, string midname, string userType, string username, string userpass, string cardCode)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(8);
        //            databaseManager.AddParameters(0, "idNumber", idNumber);
        //            databaseManager.AddParameters(1, "surname", surname);
        //            databaseManager.AddParameters(2, "firstname", firstname);
        //            databaseManager.AddParameters(3, "midname", midname);
        //            databaseManager.AddParameters(4, "userType", userType);
        //            databaseManager.AddParameters(5, "username", username);
        //            databaseManager.AddParameters(6, "userpass", userpass);
        //            databaseManager.AddParameters(7, "cardCode", cardCode);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertUser"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateUser(UInt32 uid, string surname, string firstname, string midname, string userType, string cardCode)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(6);
        //            databaseManager.AddParameters(0, "id", uid);
        //            databaseManager.AddParameters(1, "surname", surname);
        //            databaseManager.AddParameters(2, "firstname", firstname);
        //            databaseManager.AddParameters(3, "midname", midname);
        //            databaseManager.AddParameters(4, "userType", userType);
        //            databaseManager.AddParameters(5, "cardCode", cardCode);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateUser"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public UInt32 DeleteUser(UInt32 uid)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "id", uid);
        //            return Convert.ToUInt16(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "DeleteUser"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateUserPassword(UInt32 uid, string password)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", uid);
        //            databaseManager.AddParameters(1, "userpass", password);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateUserPassword"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool CountUsername(UInt32 uid, string username)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", uid);
        //            databaseManager.AddParameters(1, "username", username);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "CountUsername"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool CheckUser(UInt32 uid, string username, string userpass)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "id", uid);
        //            databaseManager.AddParameters(1, "username", username);
        //            databaseManager.AddParameters(2, "userpass", userpass);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "CheckUser"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool GetUserCountCardNumber(string cardNumber, UInt32 id)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "cardNumber", cardNumber);
        //            databaseManager.AddParameters(1, "id", id);
        //            return Convert.ToBoolean(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "GetUserCountCardNumber"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool ToggleUserActive(UInt32 uid, bool active)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", uid);
        //            databaseManager.AddParameters(1, "active", active);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "ToggleUserActive"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool CheckUser(string username, string userpass)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "username", username);
        //            databaseManager.AddParameters(1, "userpass", userpass);
        //            return Convert.ToBoolean(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "CheckUser"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}
        //#endregion

        //#region Equipment
        //public DataTable GetEquipmentAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetEquipmentAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public DataTable GetEquipmentNameInventoryList()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetEquipmentNameInventoryList");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertEquipment(string equipmentName, string description)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "equipmentName", equipmentName);
        //            databaseManager.AddParameters(1, "description", description);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertEquipment"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateEquipment(UInt32 id, string equipmentName, string description)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "equipmentName", equipmentName);
        //            databaseManager.AddParameters(2, "description", description);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateEquipment"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}
        //#endregion

        //#region Equipment List
        //public DataTable GetEquipmentListAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetEquipmentListAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertEquipmentList(string itemName, UInt32 equipmentId, string serial, bool isBroken)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(4);
        //            databaseManager.AddParameters(0, "itemName", itemName);
        //            databaseManager.AddParameters(1, "equipmentId", equipmentId);
        //            databaseManager.AddParameters(2, "serial", serial);
        //            databaseManager.AddParameters(3, "isBroken", isBroken);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertEquipmentList"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateEquipmentList(UInt32 id, string itemName, UInt32 equipmentId, string serial, bool isBroken)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(5);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "itemName", itemName);
        //            databaseManager.AddParameters(2, "equipmentId", equipmentId);
        //            databaseManager.AddParameters(3, "serial", serial);
        //            databaseManager.AddParameters(4, "isBroken", isBroken);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateEquipmentList"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public DataTable GetEquipmentListAvailable()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetEquipmentListAvailable");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}
        //#endregion

        //#region Room
        //public DataTable GetRoomAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetRoomAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertRoom(string roomName, string roomCode)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "roomName", roomName);
        //            databaseManager.AddParameters(1, "roomCode", roomCode);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertRoom"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateRoom(UInt32 id, string roomName, string roomCode)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "roomName", roomName);
        //            databaseManager.AddParameters(2, "roomCode", roomCode);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateRoom"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}
        //#endregion

        //#region Instructor
        //public DataTable GetInstructorAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetInstructorAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertInstructor(string idNumber, string title, string surname, string firstname, string midname)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(5);
        //            databaseManager.AddParameters(0, "idNumber", idNumber);
        //            databaseManager.AddParameters(1, "title",title );
        //            databaseManager.AddParameters(2, "surname", surname);
        //            databaseManager.AddParameters(3, "firstname", firstname);
        //            databaseManager.AddParameters(4, "midname",midname );
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertInstructor"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateInstructor(UInt32 id, string idNumber, string title, string surname, string firstname, string midname, bool active)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(7);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "idNumber", idNumber);
        //            databaseManager.AddParameters(2, "title", title);
        //            databaseManager.AddParameters(3, "surname", surname);
        //            databaseManager.AddParameters(4, "firstname", firstname);
        //            databaseManager.AddParameters(5, "midname", midname);
        //            databaseManager.AddParameters(6, "active", active);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateInstructor"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //#endregion

        //#region Reserve Equipment Item
        //public DataTable GetTransactionByDate(DateTime dateTime)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "currDate", dateTime);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetReserveEquipmentItemByDate");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}
        //#endregion

        //#region DTR
        //public bool InsertDtr(string cardCode)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "cardCode", cardCode);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "InsertDtr"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public DataTable GetDtr(DateTime dateTime)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "currDate", dateTime);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetDtr");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public DataTable GetInstructorDtr(UInt32 userId, UInt16 month, UInt16 year)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "userId", userId);
        //            databaseManager.AddParameters(1, "currMonth", month);
        //            databaseManager.AddParameters(2, "currYear", year);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetInstructorDtr");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}
        //#endregion

        //#region Equipment Record
        //public UInt32 InsertEquipmentRecord(string borrowerName, string borrowerIdnum, UInt32 userReserveId, DateTime userReserveFrom, DateTime userReserveTo)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(5);
        //            databaseManager.AddParameters(0, "borrowerName", borrowerName);
        //            databaseManager.AddParameters(1, "borrowerIdnum", borrowerIdnum);
        //            databaseManager.AddParameters(2, "userReserveId", userReserveId);
        //            databaseManager.AddParameters(3, "userReserveFrom", userReserveFrom);
        //            databaseManager.AddParameters(4, "userReserveTo", userReserveTo);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertEquipmentRecord"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateEquipmentRecord(UInt32 id, string borrowerName, string borrowerIdnum, DateTime userReserveFrom, DateTime userReserveTo)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(5);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "borrowerName", borrowerName);
        //            databaseManager.AddParameters(2, "borrowerIdnum", borrowerIdnum);
        //            databaseManager.AddParameters(3, "userReserveFrom", userReserveFrom);
        //            databaseManager.AddParameters(4, "userReserveTo", userReserveTo);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateEquipmentRecord"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public DataTable ReturnEquipmentItem(UInt32 id, UInt32 userId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "userId", userId);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "ReturnEquipmentItem");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //#endregion

        //#region Equipment Record List
        //public UInt32 InsertEquipmentRecordList(UInt32 equipmentListId, UInt32 equipmentRecordId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "equipmentListId", equipmentListId);
        //            databaseManager.AddParameters(1, "equipmentRecordId", equipmentRecordId);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertEquipmentRecordList"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateEquipmentRecordList(UInt32 id, UInt32 equipmentListId, UInt32 equipmentRecordId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "equipmentListId", equipmentListId);
        //            databaseManager.AddParameters(2, "equipmentRecordId", equipmentRecordId);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateEquipmentRecordList"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool DeleteEquipmentRecordList(UInt32 id)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "id", id);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "DeleteEquipmentRecordList"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        
        //#endregion

        //#region Reserve Room
        //public DataTable GetReserveRoom()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetReserveRoom");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertReserveRoom(UInt32 attendedUserId, UInt32 roomId, UInt32 instructorId, string subjectName, DateTime timeStart, DateTime timeEnd)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(6);
        //            databaseManager.AddParameters(0, "attendedUserId", attendedUserId);
        //            databaseManager.AddParameters(1, "roomId",roomId );
        //            databaseManager.AddParameters(2, "instructorId",instructorId );
        //            databaseManager.AddParameters(3, "subjectName", subjectName);
        //            databaseManager.AddParameters(4, "timeStart", timeStart);
        //            databaseManager.AddParameters(5, "timeEnd", timeEnd);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertReserveRoom"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateReserveRoom(UInt32 id, UInt32 attendedUserId, UInt32 roomId, UInt32 instructorId, string subjectName, DateTime timeStart, DateTime timeEnd)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(7);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "attendedUserId", attendedUserId);
        //            databaseManager.AddParameters(2, "roomId", roomId);
        //            databaseManager.AddParameters(3, "instructorId", instructorId);
        //            databaseManager.AddParameters(4, "subjectName", subjectName);
        //            databaseManager.AddParameters(5, "timeStart", timeStart);
        //            databaseManager.AddParameters(6, "timeEnd", timeEnd);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateReserveRoom"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public UInt16 CheckRoomAvailability(UInt32 id, DateTime currdate, UInt32 roomId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(3);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "currdate", currdate);
        //            databaseManager.AddParameters(2, "roomId", roomId);
        //            return Convert.ToUInt16(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "CheckRoomAvailability"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}
        //#endregion

        //#region Trouble Ticket
        //public DataTable GetTroubleTicketAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetTroubleTicketAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertTroubleTicket(UInt32 equipmentItemId, string defect, string resolution, string remarks, UInt32 roomId, UInt32 userId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(6);
        //            databaseManager.AddParameters(0, "equipmentItemId", equipmentItemId);
        //            databaseManager.AddParameters(1, "defect", defect);
        //            databaseManager.AddParameters(2, "resolution", resolution);
        //            databaseManager.AddParameters(3, "remarks", remarks);
        //            databaseManager.AddParameters(4, "roomId", roomId);
        //            databaseManager.AddParameters(5, "userId", userId);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertTroubleTicket"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateTroubleTicket(UInt32 id, UInt32 equipmentItemId, string defect, string resolution, string remarks, UInt32 roomId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(6);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "equipmentItemId", equipmentItemId);
        //            databaseManager.AddParameters(2, "defect", defect);
        //            databaseManager.AddParameters(3, "resolution", resolution);
        //            databaseManager.AddParameters(4, "remarks", remarks);
        //            databaseManager.AddParameters(5, "roomId", roomId);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateTroubleTicket"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        //public bool TroubleTicketCompletion(UInt32 id, UInt32 userId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "userId", userId);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "TroubleTicketCompletion"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}
        //#endregion

        //#region Reaction
        //public DataTable GetReactionByUser(UInt32 userId)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(1);
        //            databaseManager.AddParameters(0, "userId", userId);
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetReactionByUser");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public DataTable GetReactionAll()
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetReactionAll");
        //            return ToDataTable(dataSet);
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return new DataTable();
        //    }
        //}

        //public UInt32 InsertReaction(UInt32 userId, string reaction)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "userId", userId);
        //            databaseManager.AddParameters(1, "reactionBody", reaction);
        //            return Convert.ToUInt32(databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertReaction"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return 0;
        //    }
        //}

        //public bool UpdateReaction(UInt32 id, string reaction)
        //{
        //    try
        //    {
        //        using (var databaseManager = GetDatabase())
        //        {
        //            databaseManager.Open();
        //            databaseManager.CreateParameters(2);
        //            databaseManager.AddParameters(0, "id", id);
        //            databaseManager.AddParameters(1, "reactionBody", reaction);
        //            return Convert.ToBoolean(databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateReaction"));
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}
        //#endregion
    }
}